package com.insigma.yanwenlong.bookkeeping.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.insigma.yanwenlong.bookkeeping.db.jdbc.DBConnection;
import com.insigma.yanwenlong.bookkeeping.pojo.Account;

public class AccountDaoImpl implements AccountDao {

	@Override
	public List<Account> findaAccounts() throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "select * from account";
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		
		List<Account> accounts = new ArrayList<Account>();
		
		while(resultSet.next()) {
			Account account = new Account();
			account.setId(resultSet.getInt("id"));
			account.setName(resultSet.getString("name"));
			account.setInitAccount(resultSet.getDouble("initAccount"));
			account.setCurrAccount(resultSet.getDouble("currAccount"));
			account.setCreateTime(resultSet.getDate("createTime"));
			account.setUpdateTime(resultSet.getTimestamp("updateTime"));
			account.setMemberId(resultSet.getInt("memberId"));
			account.setNote(resultSet.getString("note"));
			
			accounts.add(account);
		}
		
		DBConnection.closeResultSet(resultSet);
		DBConnection.closeStatement(statement);
		DBConnection.closeConnection(connection);
		
		return accounts;
	}

	@Override
	public int addAccount(Account account) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "insert into account (name,initAccount,currAccount,createTime,memberId,note) values(?,?,?,?,?,?)";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, account.getName());
		preparedStatement.setDouble(2, account.getInitAccount());
		preparedStatement.setDouble(3, account.getCurrAccount());
		preparedStatement.setDate(4, account.getCreateTime());
		preparedStatement.setInt(5, account.getMemberId());
		preparedStatement.setString(6, account.getNote());
		
		int rowAffected = preparedStatement.executeUpdate();
		
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return rowAffected;
	}

	@Override
	public Account findAccountById(int id) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "select * from account where id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, id);
		ResultSet resultSet = preparedStatement.executeQuery();
		Account account = new Account();
		while(resultSet.next()) {
			account.setId(resultSet.getInt("id"));
			account.setName(resultSet.getString("name"));
			account.setInitAccount(resultSet.getDouble("initAccount"));
			account.setCurrAccount(resultSet.getDouble("currAccount"));
			account.setCreateTime(resultSet.getDate("createTime"));
			account.setUpdateTime(resultSet.getTimestamp("updateTime"));
			account.setMemberId(resultSet.getInt("memberId"));
			account.setNote(resultSet.getString("note"));
		}
		
		DBConnection.closeResultSet(resultSet);
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return account;
	}

	@Override
	public int updateAccount(Account account) throws SQLException {//
		Connection connection = DBConnection.getConnection();
		String sql = "update account set name = ?,initAccount = ?,currAccount = ?,memberId = ?,note = ? where id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, account.getName());
		preparedStatement.setDouble(2, account.getInitAccount());
		preparedStatement.setDouble(3, account.getCurrAccount());
		preparedStatement.setInt(4, account.getMemberId());
		preparedStatement.setString(5, account.getNote());
		preparedStatement.setInt(6, account.getId());
		
		int rowAffected = preparedStatement.executeUpdate();
		
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return rowAffected;
	}

	@Override
	public int deleteAccountById(int id) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "delete from account where id = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, id);
		
		int rowAffected = preparedStatement.executeUpdate();
		
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return rowAffected;
	}

	@Override
	public boolean deleteAccountBymemberId(int memberId) throws SQLException {
		Connection connection = DBConnection.getConnection();
		String sql = "delete from account where memberId = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, memberId);
		
		boolean isSuccess = preparedStatement.execute();
		
		DBConnection.closeStatement(preparedStatement);
		DBConnection.closeConnection(connection);
		
		return isSuccess;
	}

}
